package com.pxst.fi.service.impl;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.pxst.bank.entity.SysAvailableBankEntity;
import com.pxst.bank.entity.SysBankCardEntity;
import com.pxst.bank.entity.SysBankCardFlowEntity;
import com.pxst.bank.mapper.SysBankCardFlowEntityMapper;
import com.pxst.bank.mapper.SysBankCardMapper;
import com.pxst.bank.req.BankBillReq;
import com.pxst.exception.BusinessException;
import com.pxst.fi.entity.SysBankBillEntity;
import com.pxst.fi.service.SysBankBillService;
import com.pxst.fi.mapper.SysBankBillMapper;
import com.pxst.fx.entity.SysFxOrderEntity;
import com.pxst.internalReports.entity.*;
import com.pxst.internalReports.mapper.SysBankBillRemarkEntityMapper;
import com.pxst.internalReports.req.VirtualBankStatementReq;
import com.pxst.internalReports.req.VirtualBankStatementSearchReq;
import com.pxst.internalReports.req.VirtualBankStatementUpdateReq;
import com.pxst.internalReports.resp.VirtualBankStatementExcelResp;
import com.pxst.internalReports.resp.VirtualBankStatementSearchRespData;
import com.pxst.internalReports.service.SysBankBillModifyEntityService;
import com.pxst.internalReports.service.SysBankBillRemarkEntityService;
import com.pxst.merchant.entity.SysMerchantInfo;
import com.pxst.resp.ApiResp;
import com.pxst.utils.ExcelUtils;
import icu.mhb.mybatisplus.plugln.base.service.impl.JoinServiceImpl;
import icu.mhb.mybatisplus.plugln.core.JoinLambdaWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.*;

import static com.pxst.constants.FilePathConstant.VBSUploadExamplePath;

/**
 * @author notch
 * @description 针对表【sys_bank_bill(银行对账单（vb100）)】的数据库操作Service实现
 * @createDate 2023-09-14 17:22:14
 */
@Slf4j
@Service
public class SysBankBillServiceImpl extends JoinServiceImpl<SysBankBillMapper, SysBankBillEntity> implements SysBankBillService {

    @Resource
    private SysBankBillMapper sysBankBillMapper;

    @Resource
    private SysBankBillRemarkEntityMapper sysBankBillRemarkEntityMapper;

    @Resource
    private SysBankCardMapper sysBankCardMapper;


    @Resource
    private SysBankCardFlowEntityMapper sysBankCardFlowEntityMapper;

    @Resource
    private SysBankBillModifyEntityService sysBankBillModifyEntityService;

    @Autowired
    private SysBankBillService sysBankBillService;

    @Resource
    private SysBankBillRemarkEntityService sysBankBillRemarkEntityService;

    @Override
    public ApiResp searchVirtualBankStatement(VirtualBankStatementSearchReq searchReq) {
        JoinLambdaWrapper<SysBankBillEntity> jlw = new JoinLambdaWrapper<>(SysBankBillEntity.class);

        /**
         * SysBankBill表（主表）中的数据
         */
        jlw.eq(searchReq.getCurrency() != null, SysBankBillEntity::getCurrency, searchReq.getCurrency()).like(searchReq.getDescription() != null, SysBankBillEntity::getDescription, searchReq.getDescription()).in(searchReq.getType() != null, SysBankBillEntity::getType, searchReq.getType()).eq(searchReq.getTxnMode() != null, SysBankBillEntity::getTxnMode, searchReq.getTxnMode()).eq(searchReq.getShowVoid() != null, SysBankBillEntity::getVoid_, searchReq.getShowVoid()).between(searchReq.getTradeTimeStart() != null && searchReq.getTradeTimeEnd() != null, SysBankBillEntity::getTradeTime, searchReq.getTradeTimeStart(), searchReq.getTradeTimeEnd()).between(searchReq.getUpdatedStartDate() != null && searchReq.getUpdatedEndDate() != null, SysBankBillEntity::getUpdatedAt, searchReq.getUpdatedStartDate(), searchReq.getUpdatedEndDate()).eq(searchReq.getSysAvailableBankId() != null, SysBankBillEntity::getAvailableBankId, searchReq.getSysAvailableBankId())

                .eq(searchReq.getSysBankBillId() != null, SysBankBillEntity::getId, searchReq.getSysBankBillId()).or().eq(searchReq.getCommandId() != null, SysBankBillEntity::getCommandId, searchReq.getCommandId()).or();
        if (searchReq.getAccountCode() != null) {
            jlw.leftJoin(SysBankCardEntity.class, SysBankCardEntity::getId, SysBankBillEntity::getBankCardId).eq(SysBankCardEntity::getAccountCode, searchReq.getAccountCode()).selectAs(sbce -> {
                sbce.addFunAlias(SysBankCardEntity::getAccountCode, VirtualBankStatementSearchRespData::getAccountCode);
            }).end();

        }
        jlw.selectAs(sbb -> {
            sbb.addFunAlias(SysBankBillEntity::getId, VirtualBankStatementSearchRespData::getSysBankBillId).addFunAlias(SysBankBillEntity::getDescription, VirtualBankStatementSearchRespData::getDescription).addFunAlias(SysBankBillEntity::getDebit, VirtualBankStatementSearchRespData::getDebit).addFunAlias(SysBankBillEntity::getCredit, VirtualBankStatementSearchRespData::getCredit).addFunAlias(SysBankBillEntity::getBalanceXy, VirtualBankStatementSearchRespData::getBalanceXy).addFunAlias(SysBankBillEntity::getUpdatedAt, VirtualBankStatementSearchRespData::getUpdatedAt).addFunAlias(SysBankBillEntity::getCreatedAt, VirtualBankStatementSearchRespData::getCreatedAt).addFunAlias(SysBankBillEntity::getCreator, VirtualBankStatementSearchRespData::getCreator).addFunAlias(SysBankBillEntity::getUpdater, VirtualBankStatementSearchRespData::getUpdator).addFunAlias(SysBankBillEntity::getCurrency, VirtualBankStatementSearchRespData::getCurrency).addFunAlias(SysBankBillEntity::getType, VirtualBankStatementSearchRespData::getType).addFunAlias(SysBankBillEntity::getVoid_, VirtualBankStatementSearchRespData::getVoid_).addFunAlias(SysBankBillEntity::getDuplicateId, VirtualBankStatementSearchRespData::getDuplicateId).addFunAlias(SysBankBillEntity::getTxnMode, VirtualBankStatementSearchRespData::getTxnMode).addFunAlias(SysBankBillEntity::getVoidRequester, VirtualBankStatementSearchRespData::getVoidRequester).addFunAlias(SysBankBillEntity::getVoidApprover, VirtualBankStatementSearchRespData::getVoidApprover).addFunAlias(SysBankBillEntity::getVoidApprovedAt, VirtualBankStatementSearchRespData::getVoidApprovedAt).addFunAlias(SysBankBillEntity::getVoidRejector, VirtualBankStatementSearchRespData::getVoidRejector).addFunAlias(SysBankBillEntity::getCommandId, VirtualBankStatementSearchRespData::getCommandId).addFunAlias(SysBankBillEntity::getReference, VirtualBankStatementSearchRespData::getReference).addFunAlias(SysBankBillEntity::getBankFee, VirtualBankStatementSearchRespData::getBankFee).addFunAlias(SysBankBillEntity::getTradeTime, VirtualBankStatementSearchRespData::getTradeTime);
        });

        jlw.leftJoin(SysMerchantInfo.class, SysMerchantInfo::getId, SysBankBillEntity::getMerchantId).selectAs(mi -> {
            mi.addFunAlias(SysMerchantInfo::getName, VirtualBankStatementSearchRespData::getMerchantInfoName).addFunAlias(SysMerchantInfo::getCode, VirtualBankStatementSearchRespData::getMerchantInfoCode);
        }).end();


        jlw.leftJoin(SysAvailableBankEntity.class, SysAvailableBankEntity::getId, SysBankBillEntity::getAvailableBankId).selectAs(ab -> {
            ab.addFunAlias(SysAvailableBankEntity::getBankName, VirtualBankStatementSearchRespData::getAvailableBankName);
        }).end();

        jlw.leftJoin(SysBankBillModifyEntity.class, SysBankBillModifyEntity::getBankCardId, SysBankBillEntity::getBankCardId).selectAs(bme -> {
            bme.addFunAlias(SysBankBillModifyEntity::getBalance, VirtualBankStatementSearchRespData::getTrialBalance);
        }).end();

        jlw.leftJoin(SysFxOrderEntity.class, SysFxOrderEntity::getMerchantId, SysBankBillEntity::getMerchantId).selectAs(foe -> {
            foe.addFunAlias(SysFxOrderEntity::getTrCode, VirtualBankStatementSearchRespData::getTrCode);
        }).end();

        jlw.leftJoin(SysXyExceptionRecordEntity.class, SysXyExceptionRecordEntity::getBankBillId, SysBankBillEntity::getId).selectAs(xere -> {
            xere.add("if( (select count(*) from sys_xy_exception_record where sys_xy_exception_record.bank_bill_id = sys_bank_bill.id)>0,1,0 )", "history", false);
        }).end();

        jlw.leftJoin(SysBankBillRemarkEntity.class, SysBankBillRemarkEntity::getBankBillId, SysBankBillEntity::getId).manyToManySelect(VirtualBankStatementSearchRespData::getRemark, SysBankBillRemarkEntity.class, bbre -> {
            bbre.add(SysBankBillRemarkEntity::getId, SysBankBillRemarkEntity::getBankBillId, SysBankBillRemarkEntity::getContent, SysBankBillRemarkEntity::getRemarkBy, SysBankBillRemarkEntity::getCreator, SysBankBillRemarkEntity::getCreatedAt, SysBankBillRemarkEntity::getUpdater, SysBankBillRemarkEntity::getUpdatedAt);
        }).end();

        List<VirtualBankStatementSearchRespData> respData = sysBankBillMapper.joinSelectList(jlw, VirtualBankStatementSearchRespData.class);

        return ApiResp.sucess(respData);
    }

    @Override
    public void downloadVirtualBankStatement(VirtualBankStatementSearchReq searchReq, HttpServletResponse response) {
        JoinLambdaWrapper<SysBankBillEntity> jlw = new JoinLambdaWrapper<>(SysBankBillEntity.class);
        /**
         * SysBankBill表（主表）中的数据
         */
        jlw.eq(searchReq.getCurrency() != null, SysBankBillEntity::getCurrency, searchReq.getCurrency()).like(searchReq.getDescription() != null, SysBankBillEntity::getDescription, searchReq.getDescription()).in(searchReq.getType() != null, SysBankBillEntity::getType, searchReq.getType()).eq(searchReq.getTxnMode() != null, SysBankBillEntity::getTxnMode, searchReq.getTxnMode()).eq(searchReq.getShowVoid() != null, SysBankBillEntity::getVoid_, searchReq.getShowVoid()).between(searchReq.getTradeTimeStart() != null && searchReq.getTradeTimeEnd() != null, SysBankBillEntity::getTradeTime, searchReq.getTradeTimeStart(), searchReq.getTradeTimeEnd()).between(searchReq.getUpdatedStartDate() != null && searchReq.getUpdatedEndDate() != null, SysBankBillEntity::getUpdatedAt, searchReq.getUpdatedStartDate(), searchReq.getUpdatedEndDate()).eq(searchReq.getSysAvailableBankId() != null, SysBankBillEntity::getAvailableBankId, searchReq.getSysAvailableBankId()).eq(searchReq.getSysBankBillId() != null, SysBankBillEntity::getId, searchReq.getSysBankBillId()).or().eq(searchReq.getCommandId() != null, SysBankBillEntity::getCommandId, searchReq.getCommandId()).or();
        if (searchReq.getAccountCode() != null) {
            jlw.leftJoin(SysBankCardEntity.class, SysBankCardEntity::getId, SysBankBillEntity::getBankCardId)
                    .eq(SysBankCardEntity::getAccountCode, searchReq.getAccountCode())
                    .selectAs(sbce -> {
                        sbce.addFunAlias(SysBankCardEntity::getAccountCode, VirtualBankStatementExcelResp::getAccountCode);
                    }).end();
        }

        /**
         * sys_bank_bill表中的数据
         *  1.id
         *  2.txn_mode
         *  3.balanceXy
         *  4.description
         *  5.debit
         *  6.credit
         *  7.command_id
         *  8.type
         *  9.bank_fee
         *  10.void_
         *  11.void_requester
         *  12.void_approver
         *  13.void_rejector
         *  14.creator
         *  15.createdAt
         *  16.updated
         *  17.updatedAt
         */
        jlw.selectAs(sbb -> {
            sbb.addFunAlias(SysBankBillEntity::getId, VirtualBankStatementExcelResp::getSysBankBillId).addFunAlias(SysBankBillEntity::getDescription, VirtualBankStatementExcelResp::getDescription).addFunAlias(SysBankBillEntity::getDebit, VirtualBankStatementExcelResp::getDebit).addFunAlias(SysBankBillEntity::getCredit, VirtualBankStatementExcelResp::getCredit).addFunAlias(SysBankBillEntity::getBalanceXy, VirtualBankStatementExcelResp::getBalanceXy).addFunAlias(SysBankBillEntity::getUpdatedAt, VirtualBankStatementExcelResp::getUpdatedAt).addFunAlias(SysBankBillEntity::getCreatedAt, VirtualBankStatementExcelResp::getCreatedAt).addFunAlias(SysBankBillEntity::getCreator, VirtualBankStatementExcelResp::getCreator).addFunAlias(SysBankBillEntity::getUpdater, VirtualBankStatementExcelResp::getUpdator).addFunAlias(SysBankBillEntity::getType, VirtualBankStatementExcelResp::getType).addFunAlias(SysBankBillEntity::getVoid_, VirtualBankStatementExcelResp::getVoid_).addFunAlias(SysBankBillEntity::getTxnMode, VirtualBankStatementExcelResp::getTxnMode).addFunAlias(SysBankBillEntity::getVoidRequester, VirtualBankStatementExcelResp::getVoidRequester).addFunAlias(SysBankBillEntity::getVoidApprover, VirtualBankStatementExcelResp::getVoidApprover).addFunAlias(SysBankBillEntity::getVoidRejector, VirtualBankStatementExcelResp::getVoidRejector).addFunAlias(SysBankBillEntity::getCommandId, VirtualBankStatementExcelResp::getCommandId).addFunAlias(SysBankBillEntity::getBankFee, VirtualBankStatementExcelResp::getBankFee).addFunAlias(SysBankBillEntity::getTradeTime, VirtualBankStatementExcelResp::getTxnTime);
        });

        /**
         * 获取sys_merchant_info表中的数据
         * 1.merchant_name
         */
        jlw.leftJoin(SysMerchantInfo.class, SysMerchantInfo::getId, SysBankBillEntity::getMerchantId).selectAs(mi -> {
            mi.addFunAlias(SysMerchantInfo::getName, VirtualBankStatementExcelResp::getMerchantInfoName);
        }).end();

        /**
         * 获取sys_available_bank表中的数据
         * 1.bank_name
         */
        jlw.leftJoin(SysAvailableBankEntity.class, SysAvailableBankEntity::getId, SysBankBillEntity::getAvailableBankId).selectAs(ab -> {
            ab.addFunAlias(SysAvailableBankEntity::getBankName, VirtualBankStatementExcelResp::getAvailableBankName);
        }).end();

        /**
         * 获取sys_bank_bill_modify表中的数据
         * 1.trial_balance(balance)
         */
        jlw.leftJoin(SysBankBillModifyEntity.class, SysBankBillModifyEntity::getBankCardId, SysBankBillEntity::getBankCardId).selectAs(bme -> {
            bme.addFunAlias(SysBankBillModifyEntity::getBalance, VirtualBankStatementExcelResp::getTrialBalance);
        }).end();


        List<VirtualBankStatementExcelResp> excelResps = sysBankBillMapper.joinSelectList(jlw, VirtualBankStatementExcelResp.class);
        //创建汇出表的文件名
        String fileName = "VBS-";

        try {
            //尝试导出表
            ExcelUtils.exportExcel(excelResps, null, "Worksheet", VirtualBankStatementExcelResp.class, fileName, response);
        } catch (IOException e) {
            e.printStackTrace();
            log.error(fileName + ".xlsx 表导出失败");
        }

    }

    @Override
    public void downloadVBSUploadExample(HttpServletResponse response) {
        try {
            /**
             * 指定文件的路径
             */
            File file = new File(VBSUploadExamplePath);
            //打印路径日志
            log.info(file.getPath());

            String filename = file.getName();


            // 将文件写入输入流
            FileInputStream fileInputStream = new FileInputStream(file);
            InputStream fis = new BufferedInputStream(fileInputStream);
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();

            // 清空response
            response.reset();

            response.setCharacterEncoding("UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
            response.addHeader("Content-Length", "" + file.length());
            response.setContentType("application/octet-stream");

            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            outputStream.write(buffer);

            //清空输出流
            outputStream.flush();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    @Override
    public ApiResp createVirtualBankStatement(VirtualBankStatementReq bankStatementReq) {
        //1.根据bankCardId获取bc100表的信息
        //获取全部的信息
        LambdaQueryWrapper<SysBankCardEntity> wrapper1 = new LambdaQueryWrapper();
        wrapper1.in(bankStatementReq.getBankCardId() != null, SysBankCardEntity::getId, bankStatementReq.getBankCardId());
        List<SysBankCardEntity> sysBankCardEntities = sysBankCardMapper.selectList(wrapper1);

        List<Long> bankCardIds = new ArrayList<>();
        for (SysBankCardEntity sysBankCardEntity : sysBankCardEntities) {
            if (!ObjectUtils.isEmpty(sysBankCardEntity.getId())) {
                bankCardIds.add(sysBankCardEntity.getId());
            }
        }

        // 3.然后根据bc100的信息更新sys_bank_card_flow的信息（sys_bank_card_flow的current_balance和current_balance_xy = req的balance）
        /**
         * tips:这段插入感觉对不上就没写（QAQ有问题再加）
         */
//        LambdaQueryWrapper<SysBankCardFlowEntity> wrapper = new LambdaQueryWrapper();
//        wrapper.in(sysBankCardEntities != null, SysBankCardFlowEntity::getBankCardId, bankCardIds);
//        List<SysBankCardFlowEntity> cardFlowEntities = sysBankCardFlowEntityMapper.selectList(wrapper);
//
////        cardFlowEntity.setUpdater(bankStatementReq.getOperator());
////        cardFlowEntity.setUpdatedAt(new Date());
////            cardFlowEntity.setBalance(datum.getBalance());
////            cardFlowEntity.setXyBalance(datum.getBalance());


        //暂存待插入数据
        List<SysBankBillEntity> notInsertBankBillData = new ArrayList<>();
        List<SysBankBillRemarkEntity> notInserBankBillRemarkData = new ArrayList<>();

        //2.数据处理（根据amountActive来进行对应的amount_debit或者amount_credit的数据设置）
        for (BankBillReq datum : bankStatementReq.getData()) {
            if (datum.getAmountActive().equals("amount_debit")) {
                datum.setAmountCredit(BigDecimal.valueOf(0));
            }
            if (datum.getAmountActive().equals("amount_credit")) {
                datum.setAmountDebit(BigDecimal.valueOf(0));
            }

            //4.根据tradeId是否存在来（存在就更新，不存在就新增）进行sys_bank_bill表的新增（注意：balance_xy = balance、pre_balance_xy = balance-amount_credit+amount_debit）
            SysBankBillEntity sysBankBillEntity = new SysBankBillEntity();
            sysBankBillEntity.setTxnMode(datum.getTxnMode());
            sysBankBillEntity.setBankFee(datum.getBankFee());
            sysBankBillEntity.setAvailableBankId(bankStatementReq.getAvailableBankId());
            sysBankBillEntity.setBankCardId(bankStatementReq.getBankCardId());
            sysBankBillEntity.setBalanceXy(datum.getBalance());
            sysBankBillEntity.setBalanceXy(datum.getBalance().subtract(datum.getAmountCredit()).add(datum.getAmountDebit()));
            sysBankBillEntity.setDescription(datum.getDescription());
            sysBankBillEntity.setType(datum.getType());
            sysBankBillEntity.setTradeTime(datum.getTradeTime());
            sysBankBillEntity.setId(datum.getTradeId());
            notInsertBankBillData.add(sysBankBillEntity);

            if (datum.getType().equals(22) && !ObjectUtils.isEmpty(datum.getRemark())) {
                // 5.根据type是否等于22同时remark是否为空来进行sys_bank_bill_remark表的新建记录（remark_by字段直接填充"ALL",content = remark）
                SysBankBillRemarkEntity sysBankBillRemarkEntity = new SysBankBillRemarkEntity();
                sysBankBillRemarkEntity.setBankBillId(datum.getTradeId());
                sysBankBillRemarkEntity.setContent(datum.getRemark());
                sysBankBillRemarkEntity.setRemarkBy("ALL");
                sysBankBillRemarkEntity.setCreator(bankStatementReq.getOperator());
                sysBankBillRemarkEntity.setUpdater(bankStatementReq.getOperator());
                sysBankBillRemarkEntity.setCreatedAt(LocalDateTime.now());
                sysBankBillRemarkEntity.setUpdatedAt(LocalDateTime.now());

                notInserBankBillRemarkData.add(sysBankBillRemarkEntity);
            }

        }
        boolean batch1 = this.saveOrUpdateBatch(notInsertBankBillData);
        boolean batch2 = sysBankBillRemarkEntityService.saveOrUpdateBatch(notInserBankBillRemarkData);
        if (!batch1 || !batch2) {
            throw new BusinessException("虚拟银行对账单数据新增异常，请检查数据信息！");
        }

        return ApiResp.sucess();
    }

    @Override
    public ApiResp updateVirtualBankStatement(VirtualBankStatementUpdateReq req) {
        /**
         * 1.首先根据bankCardId以及对应的sys_bank_bill.id的范围去查询所有的sys_bank_bill的记录
         * 2.然后遍历sys_bank_bill的记录
         * 3.然后遍历对应的sys_bank_bill_modify的记录
         * 循环处理：
         * 判断sys_bank_bill_modify的balance是否存在 同时判断sys_bank_bill的balance_xy是否与对应的sys_bank_bill_modify的balance相等。
         * 如果不相等，则将sys_bank_bill_modify中的balance更改为balance_xy对应的值，同时修改更新时间和更新者信息然后保存。
         */

        //1.首先根据bankCardId以及对应的sys_bank_bill.id的范围去查询所有的sys_bank_bill的记录
        JoinLambdaWrapper<SysBankBillEntity> jlw = new JoinLambdaWrapper<>(SysBankBillEntity.class);
        jlw.eq(req.getBankCardId() != null, SysBankBillEntity::getBankCardId, req.getBankCardId()).eq(req.getCurrency() != null, SysBankBillEntity::getCurrency, req.getCurrency()).between(req.getStartNumber() != null && req.getEndNumber() != null, SysBankBillEntity::getId, req.getStartNumber(), req.getEndNumber()).selectAs(bbe -> {
            bbe.addFunAlias(SysBankBillEntity::getBalanceXy, SysBankBillAndModifyEntity::getBankbillBalanceXy);
        });

        jlw.leftJoin(SysBankBillModifyEntity.class, SysBankBillModifyEntity::getId, SysBankBillEntity::getId).selectAs(bbme -> {
                    bbme.addFunAlias(SysBankBillModifyEntity::getBalance, SysBankBillAndModifyEntity::getBillModifyBalance);
                })
                .eq(req.getBankCardId() != null, SysBankBillModifyEntity::getBankCardId, req.getBankCardId())
                .end();

        //2.遍历sys_bank_bill的记录和对应的sys_bank_bill_modify的记录
        List<SysBankBillAndModifyEntity> entityList = sysBankBillMapper.joinSelectList(jlw, SysBankBillAndModifyEntity.class);
        List<SysBankBillModifyEntity> modifyEntityList = new LinkedList<>();
        for (SysBankBillAndModifyEntity forResult : entityList) {
            //循环处理：
            //判断sys_bank_bill_modify的balance是否存在 同时判断sys_bank_bill的balance_xy是否与对应的sys_bank_bill_modify的balance相等。
            //如果不相等，则将sys_bank_bill_modify中的balance更改为balance_xy对应的值，同时修改更新时间和更新者信息然后保存。
            if (!ObjectUtils.isEmpty(forResult.getBillModifyBalance())) {
                if (!forResult.getBankbillBalanceXy().equals(forResult.getBillModifyBalance())) {
                    SysBankBillModifyEntity sysBankBillModify = new SysBankBillModifyEntity();
                    sysBankBillModify.setBalance(forResult.getBankbillBalanceXy());
                    sysBankBillModify.setUpdater(req.getOperator());
                    sysBankBillModify.setUpdatedAt(LocalDateTime.now());

                    modifyEntityList.add(sysBankBillModify);
                }
            }
        }

        try {
            //批量修改
            sysBankBillModifyEntityService.updateBatchById(modifyEntityList);
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("对账修改失败！");
        }
        return ApiResp.sucess();
    }

    @Override
    public ApiResp uploadVirtualBankStatement(MultipartFile file, HttpServletRequest request) throws Exception {
        /**
         1.首先检测上传的文件的后缀名是否为Excel表的格式
         2.转换成List集合
         3.判断数据是否超过1000行（如果超出，抛出数据过多异常）
         4.遍历数据并做处理：
         4.1：判断bank、account_code、collect_type、transaction_date、debit、credit、balance、description是否为空，如果为空，则将debit、credit、balance置为0
         4.2：然后根据bank、account_code遍历bc100表判断是否存在对应的记录
         4.3：根据bc100的open_card_status是否等于2来判断卡是否可用来抛异常
         4.4：都没问题就获取可用的BK100_ID、BC100_ID、currency
         4.5：根据collect_type设置txnMode（Bank：1、QR code：2、TrueWallet：3、Unknown：0,其他也为0）这里用switch实现
         4.6：根据debit是否为空设置amountActive（为空：amount_credit、不为空：amount_debit）、type（为空：7、不为空：8）、设置txn_mode=txnMode
         */

        //  1.首先检测上传的文件的后缀名是否为Excel表的格式
        String filename = file.getOriginalFilename();
        String last = StringUtils.substringAfterLast(filename, ".");
        if (!last.equals("xlsx") || !last.equals("xls")) {
            throw new BusinessException("上传文件格式错误！");
        }

        // 2.转换成List集合
        File tempFile = new File("file/" + filename);
        file.transferTo(tempFile);
        ImportParams params = new ImportParams();
        List<VirtualBankStatementExcelImportEntity> excelData = ExcelImportUtil.importExcel(new FileInputStream(tempFile), VirtualBankStatementExcelImportEntity.class, params);

        //3.1判断数据是否超过1000行（如果超出，抛出数据过多异常）
        if (excelData.size() > 1000) {
            throw new BusinessException("导入文件数据过多，请删减数据！");
        }

        //3.2先查出遍历时需要的数据库数据
        JoinLambdaWrapper<SysBankCardEntity> jlw = new JoinLambdaWrapper<>(SysBankCardEntity.class);
        jlw
                .selectAs(ee -> {
                    ee
                            .addFunAlias(SysBankCardEntity::getId, MapKeyCache::getBankCardId)
                            .addFunAlias(SysBankCardEntity::getAvailableBankId, MapKeyCache::getAvailableBankId)
                            .addFunAlias(SysBankCardEntity::getCurrency, MapKeyCache::getCurrency)
                            .addFunAlias(SysBankCardEntity::getAccountCode, MapKeyCache::getAccountCode);
                })
                .leftJoin(SysAvailableBankEntity.class, SysAvailableBankEntity::getId, SysBankCardEntity::getAvailableBankId)
                .selectAs(es -> {
                    es.addFunAlias(SysAvailableBankEntity::getBankCode, MapKeyCache::getBankCode);
                })
                .end();
        List<MapKeyCache> mapKeyCache = sysBankCardMapper.joinSelectList(jlw, MapKeyCache.class);
        if (ObjectUtils.isEmpty(mapKeyCache)) {
            throw new BusinessException("数据异常！");
        }

        Map<MapKeyCache, List<BankBillReq>> billReqListMap = new HashMap<>();
        for (MapKeyCache cache : mapKeyCache) {
            billReqListMap.put(cache, null);
        }

        // 4.遍历数据并做处理：
        for (VirtualBankStatementExcelImportEntity excelDatum : excelData) {
            BankBillReq bankBillReq = new BankBillReq();

            // 4.1：判断bank、account_code、collect_type、transaction_date、debit、credit、balance、description是否为空，如果为空，则将debit、credit、balance置为0
            if (excelDatum.getBank() == null || excelDatum.getCollectType() == null || excelDatum.getTradeTime() == null || excelDatum.getCollectType() == null ||
                    excelDatum.getBalance() == null) {
                log.error("数据异常");
                throw new BusinessException("数据异常");
            }
            if (excelDatum.getDebit() == null && excelDatum.getCredit() == null) {
                log.error("数据异常");
                throw new BusinessException("数据异常");
            }

            if (excelDatum.getCredit() == null) {
                excelDatum.setCredit(BigDecimal.ZERO);
                bankBillReq.setAmountActive("amount_debit");
            }
            if (excelDatum.getDebit() == null) {
                excelDatum.setDebit(BigDecimal.ZERO);

                // 4.6：根据debit是否为空设置amountActive（为空：amount_credit、不为空：amount_debit）、type（为空：7、不为空：8）、设置txn_mode=txnMode
                bankBillReq.setAmountActive("amount_credit");
                bankBillReq.setType(7);
            }
            bankBillReq.setType(8);

            if (excelDatum.getBalance() == null) {
                excelDatum.setBalance(BigDecimal.ZERO);
            }

            // 4.5：根据collect_type设置txnMode（Bank：1、QR code：2、TrueWallet：3、Unknown：0,其他也为0）这里用switch实现
            switch (excelDatum.getCollectType()) {
                case "Bank":
                    bankBillReq.setTxnMode(1);
                    break;
                case "QR code":
                    bankBillReq.setTxnMode(2);
                    break;
                case "TrueWallet":
                    bankBillReq.setTxnMode(3);
                    break;
                case "Unknow":
                default:
                    bankBillReq.setTxnMode(0);
                    break;
            }

            //拿着对应的key信息去map集合遍历并插入
            billReqListMap.forEach((k, v) -> {
                if (k.getBankCode().equals(excelDatum.getBank()) && k.getAccountCode().equals(excelDatum.getAccountCode())) {
                    v.add(bankBillReq);
                }
            });
        }

        //将处理好的数据进行遍历，筛选出有data数据的一组数据进行接下来的新增操作
        List<VirtualBankStatementReq> statementReqList = new ArrayList<>();
        billReqListMap.forEach((k, v) -> {
            if (!ObjectUtils.isEmpty(v)) {
                VirtualBankStatementReq temp = new VirtualBankStatementReq();
                temp.setCurrency(k.getCurrency());
                temp.setAvailableBankId(k.getAvailableBankId());
                temp.setBankCardId(k.getBankCardId());
                temp.setData(v);
                temp.setOperator(request.getHeader("operator"));

                sysBankBillService.createVirtualBankStatement(temp);
            }
        });
        return ApiResp.sucess();
    }

}




